package co.com.inventarios.persistencia;

import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Date;
import java.util.logging.Level;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import co.com.inventarios.modelo.RequiDoc;
import co.com.inventarios.modelo.RequiDocId;

public class QueryRequisicionDAO {

	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}

	public RequiDoc findByProperty(String propertyName, RequiDocId value) {
		// TODO Auto-generated method stub
		EntityManagerHelper.log("finding RequiDoc instance with property: "
				+ propertyName + ", value: " + value, Level.INFO, null);
		try {
			getEntityManager().clear();
			final String queryString = "select model from RequiDoc model where model."
					+ propertyName
					+ "= :propertyValue     ORDER BY model.id.reqnumero DESC ";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue", value);
			return (RequiDoc) query.getSingleResult();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findByPropertyLike(String propertyName, Object value) {
		EntityManagerHelper.log("finding RequiDoc instance with property: "
				+ propertyName + ", value: " + value, Level.INFO, null);
		try {
			getEntityManager().clear();
			final String queryString = "select model from RequiDoc model where model."
					+ propertyName
					+ " LIKE '%"
					+ value
					+ "%'   ORDER BY model.id.reqnumero DESC ";
			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findByProperty_list(String bodegas, String clase,
			String tipo, String propertyName, String value) {
		EntityManagerHelper.log("finding RequiDoc instance with id: " + clase,
				Level.INFO, null);
		try {

			String[] Bod = null;
			String filtro = "";
			if (bodegas != null && bodegas != "") {
				Bod = bodegas.split(",");
				for (int i = 0; i < Bod.length; i++) {

					if (i == 0) {
						filtro = filtro + " AND ( model.bodega.bodcodigo ='"
								+ Bod[i] + "' ";
					} else {
						filtro = filtro + " OR model.bodega.bodcodigo ='"
								+ Bod[i] + "' ";
					}

					if (i == (Bod.length - 1)) {
						filtro = filtro + ")";
					}
				}
			} else {
				filtro = " AND model.bodega.bodcodigo= 'X' ";
			}
			
			Date today = new Date();
//			+ " = to_Date("
//			+ today
//			+ ",'MM/DD/YYYY HH24:MI:SS') ORDER BY model.id.reqnumero DESC";
//			

			// RequiDoc a = null;
			getEntityManager().clear();

			if (propertyName.toString() == String.valueOf("reqnumero")) {
				final String queryString = "select model from RequiDoc model where model.id.reqclase='"
						+ clase
						+ "' "
						+ filtro
						+ " and   model.id.reqtipo='"
						+ tipo
						+ "' and "
						+ propertyName
						+ " = "
						+ Double.valueOf(value)
						+ " ORDER BY model.id.reqnumero DESC";
				Query query = getEntityManager().createQuery(queryString);
				return query.getResultList();
			} else {
				if (propertyName.toString() == String.valueOf("reqenvfec")) {
					//value = value.replace("/", "");
					SimpleDateFormat formato = new SimpleDateFormat("MM/dd/yy");
					value = formato.format(today);
					final String queryString = "select model from RequiDoc model where model.id.reqclase='"
							+ clase
							+ "' "
							+ filtro
							+ " and   model.id.reqtipo='"
							+ tipo
							+ "' and "
							+ propertyName.toString()
							+ " = to_Date('"
							+ value
							+ "','MM/DD/YYYY HH24:MI:SS') ORDER BY model.id.reqnumero DESC";
					Query query = getEntityManager().createQuery(queryString);
					return query.getResultList();
				} else {
					final String queryString = "select model from RequiDoc model where model.id.reqclase='"
							+ clase
							+ "' "
							+ filtro
							+ " and   model.id.reqtipo='"
							+ tipo
							+ "' and "
							+ propertyName
							+ " = '"
							+ value
							+ "' ORDER BY model.id.reqnumero DESC";
					Query query = getEntityManager().createQuery(queryString);
					return query.getResultList();
				}
			}

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	
	
	
	public List<RequiDoc> findByProperty_list_Date(String bodegas, String clase,
			String tipo, String propertyName, Date value) {
		EntityManagerHelper.log("finding RequiDoc instance with id: " + clase,
				Level.INFO, null);
		try {

			String[] Bod = null;
			String filtro = "";
			if (bodegas != null && bodegas != "") {
				Bod = bodegas.split(",");
				for (int i = 0; i < Bod.length; i++) {

					if (i == 0) {
						filtro = filtro + " AND ( model.bodega.bodcodigo ='"
								+ Bod[i] + "' ";
					} else {
						filtro = filtro + " OR model.bodega.bodcodigo ='"
								+ Bod[i] + "' ";
					}

					if (i == (Bod.length - 1)) {
						filtro = filtro + ")";
					}
				}
			} else {
				filtro = " AND model.bodega.bodcodigo= 'X' ";
			}
			
			getEntityManager().clear();

					//value = value.replace("/", "");
					SimpleDateFormat formato = new SimpleDateFormat("MM/dd/yyyy");
					String valueStrA = "";
					String valueStrB = "";
					
					valueStrA = formato.format(value);
					
					long fecha = value.getTime();
					long aumento = fecha;
					aumento = fecha + 86400000;
					Date fechaMax = new Date(aumento);
					valueStrB = formato.format(fechaMax);
					
					
					

					final String queryString = "select model from RequiDoc model where model.id.reqclase='"
							+ clase
							+ "' "
							+ filtro
							+ " and   model.id.reqtipo='"
							+ tipo
							+ "' and ("
							+ propertyName.toString()
							+ " >= to_Date('"
							+ valueStrA+""
							+ "','MM/DD/YYYY') AND "
							+ propertyName.toString()
							+ " <= to_Date('"
							+ valueStrB+""
							+ "','MM/DD/YYYY') )"
							+" ORDER BY model.id.reqnumero DESC";
					Query query = getEntityManager().createQuery(queryString);
					return query.getResultList();


		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	
	
	
	
	
	
	
	
	
	public List<RequiDoc> findByProperty_listCerradas(String clase,
			String tipo, String propertyName, String value) {
		EntityManagerHelper.log("finding RequiDoc instance with id: " + clase,
				Level.INFO, null);
		try {
			// RequiDoc a = null;
			getEntityManager().clear();

			if (propertyName == "reqnumero") {
				final String queryString = "select model from RequiDoc model where model.id.reqclase='"
						+ clase
						+ "' and model.id.reqtipo='"
						+ tipo
						+ "' and "
						+ "model.id."
						+ propertyName
						+ " = "
						+ Double.valueOf(value)
						+ "  ORDER BY model.id.reqnumero DESC";
				Query query = getEntityManager().createQuery(queryString);
				return query.getResultList();
			} else {
				final String queryString = "select model from RequiDoc model where model.id.reqclase='"
						+ clase
						+ "' and model.id.reqtipo='"
						+ tipo
						+ "' and "
						+ propertyName
						+ " = '"
						+ value
						+ "' ORDER BY model.id.reqnumero DESC";
				Query query = getEntityManager().createQuery(queryString);
				return query.getResultList();
			}

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findByProperty_listCerradas_Bod(String clase,
			String bodega, String propertyName, String value) {
		EntityManagerHelper.log("finding RequiDoc instance with id: " + clase,
				Level.INFO, null);
		try {
			// RequiDoc a = null;

			String[] Bod = null;
			String filtro = "";
			Bod = bodega.split(",");
			for (int i = 0; i < Bod.length; i++) {

				if (i == 0) {
					filtro = filtro + " AND ( model.reqbodsale ='" + Bod[i]
							+ "' ";
				} else {
					filtro = filtro + " OR model.reqbodsale ='" + Bod[i] + "' ";
				}

				if (i == (Bod.length - 1)) {
					filtro = filtro + ")";
				}
			}

			getEntityManager().clear();

			if (propertyName == "reqnumero") {
				final String queryString = "select model from RequiDoc model where model.label.lbcodnum = 3 and model.id.reqclase='"
						+ clase
						+ "' and "
						+ "model.id."
						+ propertyName
						+ " = "
						+ Double.valueOf(value)
						+ " "
						+ filtro
						+ "  ORDER BY model.id.reqnumero DESC";
				Query query = getEntityManager().createQuery(queryString);
				return query.getResultList();
			} else {
				if (propertyName == ".") {
					final String queryString = "select model from RequiDoc model where model.label.lbcodnum = 3 and model.id.reqclase='"
							+ clase
							+ "' "
							+ filtro
							+ "  ORDER BY model.id.reqnumero DESC";
					Query query = getEntityManager().createQuery(queryString);
					return query.getResultList();
				} else {
					if (propertyName == "reqbodsale") {
						// System.out.print(" rrrrrrrrrrrrrrrrrrrr reqbodsale");
						final String queryString = "select model from RequiDoc model where model.label.lbcodnum = 3 and model.id.reqclase='"
								+ clase
								+ "' and "
								+ "model."
								+ propertyName
								+ " = '"
								+ value
								+ "' "
								+ "ORDER BY model.id.reqnumero DESC";
						Query query = getEntityManager().createQuery(
								queryString);
						return query.getResultList();
					} else {
						// System.out.print(" rrrrrrrrrrrrrrrrrrrr otrosss");
						final String queryString = "select model from RequiDoc model where model.label.lbcodnum = 3 and model.id.reqclase='"
								+ clase
								+ "'  and model."
								+ propertyName
								+ " = '"
								+ value
								+ "' "
								+ filtro
								+ " ORDER BY model.id.reqnumero DESC";
						Query query = getEntityManager().createQuery(
								queryString);
						return query.getResultList();
					}
				}
			}

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findByProperty_listCerradas_Bod_Date(String clase,
			String bodega, String propertyName, Date value) {
		EntityManagerHelper.log("finding RequiDoc instance with id: " + clase,
				Level.INFO, null);
		try {


			String[] Bod = null;
			String filtro = "";
			Bod = bodega.split(",");
			for (int i = 0; i < Bod.length; i++) {

				if (i == 0) {
					filtro = filtro + " AND ( model.reqbodsale ='" + Bod[i]
							+ "' ";
				} else {
					filtro = filtro + " OR model.reqbodsale ='" + Bod[i] + "' ";
				}

				if (i == (Bod.length - 1)) {
					filtro = filtro + ")";
				}
			}

			getEntityManager().clear();

			//value = value.replace("/", "");
			SimpleDateFormat formato = new SimpleDateFormat("MM/dd/yyyy");
			String valueStrA = "";
			String valueStrB = "";
			
			valueStrA = formato.format(value);
			
			long fecha = value.getTime();
			long aumento = fecha;
			aumento = fecha + 86400000;
			Date fechaMax = new Date(aumento);
			valueStrB = formato.format(fechaMax);
			
						final String queryString = "select model from RequiDoc model where model.label.lbcodnum = 3 and model.id.reqclase='"
								+ clase
								+ "'  and  ( model."
								+ propertyName.toString()
								+ " >= to_Date('"
								+ valueStrA+""
								+ "','MM/DD/YYYY') AND "
								+ propertyName.toString()
								+ " <= to_Date('"
								+ valueStrB+"" 
								+ "','MM/DD/YYYY') ) "
								+ filtro
								+ " ORDER BY model.id.reqnumero DESC";
						
						
//						+ "' and ("
//						+ propertyName.toString()
//						+ " >= to_Date('"
//						+ valueStrA+""
//						+ "','MM/DD/YYYY') AND "
//						+ propertyName.toString()
//						+ " <= to_Date('"
//						+ valueStrB+""
//						+ "','MM/DD/YYYY') )"
//						+" ORDER BY model.id.reqnumero DESC";			
						
						
						
						
						
						
						Query query = getEntityManager().createQuery(
								queryString);
						return query.getResultList();


		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	
	
	
	public List<RequiDoc> findByProperty_listBodega(String clase, String tipo,
			String propertyName, String value, String propertyNameb,
			String valueb) {
		EntityManagerHelper.log("finding RequiDoc instance with id: " + clase,
				Level.INFO, null);
		try {
			getEntityManager().clear();
			if (clase == ".") {
				final String queryString = "select model from RequiDoc model where  "
						+ propertyName
						+ " = '"
						+ value
						+ "' AND "
						+ propertyNameb
						+ " = '"
						+ valueb
						+ "' ORDER BY model.id.reqnumero DESC";
				Query query = getEntityManager().createQuery(queryString);
				return query.getResultList();
			} else {
				final String queryString = "select model from RequiDoc model where model.id.reqclase='"
						+ clase
						+ "' and "
						+ propertyName
						+ " = '"
						+ value
						+ "' AND "
						+ propertyNameb
						+ " = '"
						+ valueb
						+ "' ORDER BY model.id.reqnumero DESC";
				Query query = getEntityManager().createQuery(queryString);
				return query.getResultList();
			}

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findAll(String bodegas) {
		try {

			String[] Bod = null;
			String filtro = "";
			if (bodegas != null && bodegas != "") {
				Bod = bodegas.split(",");
				for (int i = 0; i < Bod.length; i++) {

					if (i == 0) {
						filtro = filtro + "  ( model.bodega.bodcodigo ='"
								+ Bod[i] + "' ";
					} else {
						filtro = filtro + " OR model.bodega.bodcodigo ='"
								+ Bod[i] + "' ";
					}

					if (i == (Bod.length - 1)) {
						filtro = filtro + ")";
					}
				}
			} else {
				filtro = " model.bodega.bodcodigo= 'X' ";
			}
			getEntityManager().clear();

			final String queryString = "select model from RequiDoc model where  "
					+ filtro + "  " + " ORDER BY model.id.reqnumero DESC";
			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findAll_Estado(String bodegas, Long Estado) {
		try {

			String[] Bod = null;
			String filtro = "";

			if (bodegas != null && bodegas != "") {

				Bod = bodegas.split(",");
				for (int i = 0; i < Bod.length; i++) {

					if (i == 0) {
						filtro = filtro + "  ( model.bodega.bodcodigo ='"
								+ Bod[i] + "' ";
					} else {
						filtro = filtro + " OR model.bodega.bodcodigo ='"
								+ Bod[i] + "' ";
					}

					if (i == (Bod.length - 1)) {
						filtro = filtro + ")";
					}
				}
			} else {
				filtro = " model.bodega.bodcodigo= 'X' ";
			}
			getEntityManager().clear();
			final String queryString = "select model from RequiDoc model where "
					+ filtro
					+ "  "
					+ " and  model.label.lbcodnum ="
					+ Estado
					+ " " + " ORDER BY model.id.reqnumero DESC";
			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findAllR() {
		try {
			getEntityManager().clear();
			final String queryString = "select model from RequiDoc model  "
					+ " ORDER BY model.id.reqnumero DESC";
			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findAllR_Estado(Long Estado) {
		try {
			getEntityManager().clear();
			final String queryString = "select model from RequiDoc model  "
					+ " where model.label.lbcodnum =" + Estado + " "
					+ " ORDER BY model.id.reqnumeroDESC";
			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<RequiDoc> findByProperty_listdate(String clase, String tipo,
			String propertyName, Date value) {
		EntityManagerHelper.log("finding RequiDoc instance with id: " + clase,
				Level.INFO, null);
		try {
			getEntityManager().clear();
			// SimpleDateFormat formato = new SimpleDateFormat("yyyy/MM/dd G
			// 'at'
			// hh:mm:ss z");
			SimpleDateFormat formato = new SimpleDateFormat("dd/MM/yy");

			String cadenaFecha = formato.format(value);
			// RequiDoc a = null;
			final String queryString = "select model from RequiDoc model where model.id.reqclase='"
					+ clase
					+ "' and model.id.reqtipo='"
					+ tipo
					+ "' and "
					+ propertyName
					+ " = '"
					+ cadenaFecha
					+ "' ORDER BY model.id.reqnumero DESC";
			Query query = getEntityManager().createQuery(queryString);

			return query.getResultList();

		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

}
